import { getObjType } from '../utils/util'
import { isRealNum, isRealNull, valueIsError } from './validate'
import { update } from './format'
import formula from './formula'
import dayjs from 'dayjs'

const error = {
  v: '#VALUE!', //错误的参数或运算符
  n: '#NAME?', //公式名称错误
  na: '#N/A', //函数或公式中没有可用数值
  r: '#REF!', //删除了由其他公式引用的单元格
  d: '#DIV/0!', //除数是0或空单元格
  nm: '#NUM!', //当公式或函数中某个数字有问题时
  nl: '#NULL!', //交叉运算符（空格）使用不正确
  sp: '#SPILL!', //数组范围有其它值
}

const func_methods = {
  //单元格数据生成一维数组
  getCellDataArr: function(rangeObj, nullCellType, isNeglectNullCell) {
    let dataArr = []

    if (rangeObj.data == null) {
      if (!isNeglectNullCell) {
        if (nullCellType === 'number') {
          dataArr.push(0)
        } else if (nullCellType === 'text') {
          dataArr.push('')
        }
      }
    } else {
      if (getObjType(rangeObj.data) === 'array') {
        for (let i = 0; i < rangeObj.data.length; i++) {
          for (let j = 0; j < rangeObj.data[i].length; j++) {
            let cell = rangeObj.data[i][j]
            let value = cell

            if (getObjType(cell) === 'object') {
              value = cell.v
            }

            if (value == null) {
              if (!isNeglectNullCell) {
                if (nullCellType === 'number') {
                  value = 0
                } else if (nullCellType === 'text') {
                  value = ''
                }

                dataArr.push(value)
              }
            } else {
              dataArr.push(value)
            }
          }
        }
      } else {
        if (isRealNull(rangeObj.data.v)) {
          if (!isNeglectNullCell) {
            if (nullCellType == 'number') {
              dataArr.push(0)
            } else if (nullCellType == 'text') {
              dataArr.push('')
            }
          }
        } else {
          dataArr.push(rangeObj.data.v)
        }
      }
    }

    return dataArr
  },
  //单元格数据生成二维数组
  getCellDataDyadicArr: function(rangeObj, nullCellType) {
    let dataArr = []

    if (rangeObj.data == null) {
      let rowArr = []

      if (nullCellType == 'number') {
        rowArr.push(0)
      } else if (nullCellType == 'text') {
        rowArr.push('')
      }

      dataArr.push(rowArr)
    } else {
      if (getObjType(rangeObj.data) == 'array') {
        for (let i = 0; i < rangeObj.data.length; i++) {
          let rowArr = []

          for (let j = 0; j < rangeObj.data[i].length; j++) {
            let cell = rangeObj.data[i][j]
            let value = cell

            if (getObjType(cell) === 'object') {
              value = cell.v
            }

            if (value == null) {
              if (nullCellType === 'number') {
                value = 0
              } else if (nullCellType === 'text') {
                value = ''
              }
            }

            rowArr.push(value)
          }

          dataArr.push(rowArr)
        }
      } else {
        let rowArr = []

        let value = rangeObj.data.v

        if (isRealNull(value)) {
          if (nullCellType == 'number') {
            value = 0
          } else if (nullCellType == 'text') {
            value = ''
          }
        }

        rowArr.push(value)

        dataArr.push(rowArr)
      }
    }

    return dataArr
  },
  //数组数据生成一维数组
  getDataArr: function(arr, isNeglectNaN) {
    let dataArr = []

    if (isNeglectNaN == null) {
      isNeglectNaN = false
    }

    if (getObjType(arr[0]) == 'array') {
      for (let i = 0; i < arr.length; i++) {
        for (let j = 0; j < arr[i].length; j++) {
          if (isNeglectNaN && !isRealNum(arr[i][j])) {
            continue
          }

          dataArr.push(arr[i][j])
        }
      }
    } else {
      for (let i = 0; i < arr.length; i++) {
        if (isNeglectNaN && !isRealNum(arr[i])) {
          continue
        }

        dataArr.push(arr[i])
      }
    }

    return dataArr
  },
  getDataDyadicArr: function(arr) {
    let dataArr = []

    if (getObjType(arr[0]) == 'array') {
      for (let i = 0; i < arr.length; i++) {
        let rowArr = []

        for (let j = 0; j < arr[i].length; j++) {
          rowArr.push(arr[i][j])
        }

        dataArr.push(rowArr)
      }
    } else {
      let rowArr = []

      for (let i = 0; i < arr.length; i++) {
        rowArr.push(arr[i])
      }

      dataArr.push(rowArr)
    }

    return dataArr
  },
  //是否是规则二维数组
  isDyadicArr: function(arr) {
    let isDyadic = true

    if (arr.length > 1) {
      let collen = arr[0].length

      for (let i = 1; i < arr.length; i++) {
        if (arr[i].length != collen) {
          isDyadic = false
          break
        }
      }
    }

    return isDyadic
  },
  //获取单个单元格数据，数组第一个值
  getFirstValue: function(data, nullCellType) {
    let _this = this

    if (nullCellType == null) {
      nullCellType = 'number'
    }

    let value

    if (getObjType(data) == 'array') {
      if (getObjType(data[0]) == 'array') {
        if (!_this.isDyadicArr(data)) {
          return error.v
        }

        value = data[0][0]
      } else {
        value = data[0]
      }
    } else if (getObjType(data) == 'object' && data.startCell != null) {
      if (data.data == null) {
        if (nullCellType == 'number') {
          value = 0
        } else if (nullCellType == 'text') {
          value = ''
        }
      } else {
        let cell_r = window.luckysheetCurrentRow
        let cell_c = window.luckysheetCurrentColumn

        if (data.rowl == 1 && data.coll == 1) {
          value = data.data

          if (value == null || isRealNull(value.v)) {
            if (nullCellType == 'number') {
              value = 0
            } else if (nullCellType == 'text') {
              value = ''
            }
          } else {
            value = value.v
          }
        } else {
          if (
            data.data[0][0].mc != null &&
            data.data[0][0].mc.rs == data.rowl &&
            data.data[0][0].mc.cs == data.coll
          ) {
            value = data.data[0][0]

            if (value == null || isRealNull(value.v)) {
              if (nullCellType == 'number') {
                value = 0
              } else if (nullCellType == 'text') {
                value = ''
              }
            } else {
              value = value.v
            }
          } else if (data.rowl == 1 || data.coll == 1) {
            let cellrange = formula.getcellrange(data.startCell)
            let str = cellrange.row[0],
              edr = str + data.rowl - 1,
              stc = cellrange.column[0],
              edc = stc + data.coll - 1

            if (data.rowl == 1) {
              if (cell_c < stc || cell_c > edc) {
                return error.v
              }

              value = data.data[0][cell_c - stc]
            } else if (data.coll == 1) {
              if (cell_r < str || cell_r > edr) {
                return error.v
              }

              value = data.data[cell_r - str][0]
            }

            if (value == null || isRealNull(value.v) || value.mc != null) {
              if (nullCellType == 'number') {
                value = 0
              } else if (nullCellType == 'text') {
                value = ''
              }
            } else {
              value = value.v
            }
          } else {
            return error.v
          }
        }
      }
    } else {
      value = data
    }

    return value
  },
  //获取单元格的逻辑值
  getCellBoolen: function(data) {
    let _this = this

    let cumulative = _this.getFirstValue(data)

    if (valueIsError(cumulative)) {
      return cumulative
    }

    if (getObjType(cumulative) == 'boolean') {
    } else if (
      getObjType(cumulative) == 'string' &&
      (cumulative.toLowerCase() == 'true' ||
        cumulative.toLowerCase() == 'false')
    ) {
      if (cumulative.toLowerCase() == 'true') {
        cumulative = true
      } else if (cumulative.toLowerCase() == 'false') {
        cumulative = false
      }
    } else if (isRealNum(cumulative)) {
      cumulative = parseFloat(cumulative)

      cumulative = cumulative == 0 ? false : true
    } else {
      return error.v
    }

    return cumulative
  },
  //获取单元格的日期
  getCellDate: function(data) {
    let _this = this

    let date_text

    if (getObjType(data) == 'array') {
      if (getObjType(data[0]) == 'array') {
        if (!_this.isDyadicArr(data)) {
          return error.v
        }

        date_text = data[0][0]
      } else {
        date_text = data[0]
      }
    } else if (getObjType(data) == 'object' && data.startCell != null) {
      if (
        data.data == null ||
        getObjType(data.data) == 'array' ||
        isRealNull(data.data.v)
      ) {
        return error.v
      }

      date_text = data.data.v

      if (data.data.ct != null && data.data.ct.t == 'd') {
        date_text = update('YYYY-MM-DD h:mm:ss', date_text)
      }
    } else {
      date_text = data
    }

    return date_text
  },
  getCellrangeDate: function(data) {
    let _this = this

    let date = []

    if (getObjType(data) == 'array') {
      if (getObjType(data[0]) == 'array' && !_this.isDyadicArr(data)) {
        return error.v
      }

      date = date.concat(_this.getDataArr(data, false))
    } else if (getObjType(data) == 'object' && data.startCell != null) {
      if (data.data == null) {
        date.push(0)
      } else {
        if (getObjType(data.data) == 'array') {
          for (let i = 0; i < data.data.length; i++) {
            for (let j = 0; j < data.data[i].length; j++) {
              if (data.data[i][j] != null && !isRealNull(data.data[i][j].v)) {
                let value = data.data[i][j].v

                if (data.data[i][j].ct != null && data.data[i][j].ct.t == 'd') {
                  value = update('YYYY-MM-DD h:mm:ss', value)
                }

                date.push(value)
              } else {
                date.push(0)
              }
            }
          }
        } else {
          let value = data.data.v

          if (data.data.ct != null && data.data.ct.t == 'd') {
            value = update('YYYY-MM-DD h:mm:ss', value)
          }

          date.push(value)
        }
      }
    } else {
      date.push(data)
    }

    return date
  },
  //获取正则字符串（处理 . * ? ~* ~?）
  getRegExpStr: function(str) {
    return str
      .replace('~*', '\\*')
      .replace('~?', '\\?')
      .replace('.', '\\.')
      .replace('*', '.*')
      .replace('?', '.')
  },
  //阶乘
  factorial: function(num) {
    if (num == 0 || num == 1) {
      return 1
    } else {
      return num * this.factorial(num - 1)
    }
  },
  //双阶乘
  factorialDouble: function(num) {
    if (num <= 0) {
      return 1
    } else {
      return num * this.factorialDouble(num - 2)
    }
  },
  //总体方差
  variance: function(num_arr) {
    let sum = 0,
      count = 0

    for (let i = 0; i < num_arr.length; i++) {
      let number = num_arr[i]

      sum += number
      count++
    }

    let avg = sum / count

    let sum_variance = 0

    for (let j = 0; j < num_arr.length; j++) {
      let number = num_arr[j]

      sum_variance += (number - avg) * (number - avg)
    }

    return sum_variance / count
  },
  //样本方差
  variance_s: function(num_arr) {
    let sum = 0,
      count = 0

    for (let i = 0; i < num_arr.length; i++) {
      let number = num_arr[i]

      sum += number
      count++
    }

    let avg = sum / count

    let sum_variance = 0

    for (let j = 0; j < num_arr.length; j++) {
      let number = num_arr[j]

      sum_variance += (number - avg) * (number - avg)
    }

    return sum_variance / (count - 1)
  },
  //总体标准偏差
  standardDeviation: function(num_arr) {
    let sum = 0,
      count = 0

    for (let i = 0; i < num_arr.length; i++) {
      let number = num_arr[i]

      sum += number
      count++
    }

    let avg = sum / count

    let sum_variance = 0

    for (let j = 0; j < num_arr.length; j++) {
      let number = num_arr[j]

      sum_variance += (number - avg) * (number - avg)
    }

    return Math.sqrt(sum_variance / count)
  },
  //样本标准偏差
  standardDeviation_s: function(num_arr) {
    let sum = 0,
      count = 0

    for (let i = 0; i < num_arr.length; i++) {
      let number = num_arr[i]

      sum += number
      count++
    }

    let avg = sum / count

    let sum_variance = 0

    for (let j = 0; j < num_arr.length; j++) {
      let number = num_arr[j]

      sum_variance += (number - avg) * (number - avg)
    }

    return Math.sqrt(sum_variance / (count - 1))
  },
  //是否是闰年
  isLeapYear: function(year) {
    return new Date(year, 1, 29).getMonth() === 1
  },
  //2月是否有29
  feb29Between: function(date1, date2) {
    let _this = this

    let year1 = dayjs(date1).year()
    let mar1year1 = dayjs().set({ year: year1, month: 2, date: 1 })

    if (
      _this.isLeapYear(year1) &&
      dayjs(date1) < dayjs(mar1year1) &&
      dayjs(date2) >= dayjs(mar1year1)
    ) {
      return true
    }

    let year2 = dayjs(date2).year()
    let mar1year2 = dayjs().set({ year: year2, month: 2, date: 1 })

    return (
      _this.isLeapYear(year2) &&
      dayjs(date2) >= dayjs(mar1year2) &&
      dayjs(date1) < dayjs(mar1year2)
    )
  },
  //SQL 查询
  findResultIndex: function(database, criterias) {
    let matches = {}

    for (let i = 1; i < database[0].length; ++i) {
      matches[i] = true
    }

    let maxCriteriaLength = criterias[0].length

    for (i = 1; i < criterias.length; ++i) {
      if (criterias[i].length > maxCriteriaLength) {
        maxCriteriaLength = criterias[i].length
      }
    }

    for (let k = 1; k < database.length; ++k) {
      for (let l = 1; l < database[k].length; ++l) {
        let currentCriteriaResult = false
        let hasMatchingCriteria = false

        for (let j = 0; j < criterias.length; ++j) {
          let criteria = criterias[j]

          if (criteria.length < maxCriteriaLength) {
            continue
          }

          let criteriaField = criteria[0]

          if (database[k][0] !== criteriaField) {
            continue
          }

          hasMatchingCriteria = true

          for (let p = 1; p < criteria.length; ++p) {
            currentCriteriaResult =
              currentCriteriaResult ||
              new Function('return ' + database[k][l] + criteria[p])() // jshint ignore:line
          }
        }

        if (hasMatchingCriteria) {
          matches[l] = matches[l] && currentCriteriaResult
        }
      }
    }

    let result = []

    for (let n = 0; n < database[0].length; ++n) {
      if (matches[n]) {
        result.push(n - 1)
      }
    }

    return result
  },
  findField: function(database, title) {
    let index = null

    for (let i = 0; i < database.length; i++) {
      if (database[i][0] == title) {
        index = i
        break
      }
    }

    if (index == null) {
      return error.v
    }

    return index
  },
  rest: function(array, idx) {
    idx = idx || 1

    if (!array || typeof array.slice !== 'function') {
      return array
    }

    return array.slice(idx)
  },
  compact: function(array) {
    if (!array) {
      return array
    }

    let result = []

    for (let i = 0; i < array.length; ++i) {
      if (!array[i]) {
        continue
      }

      result.push(array[i])
    }

    return result
  },
}

export default func_methods
